"""
Excel导出服务
"""
import os
import logging
from datetime import datetime
from typing import Dict, Any, List
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

from app.core.config import settings
from app.utils.helpers import sanitize_filename

logger = logging.getLogger(__name__)


class ExcelExportService:
    """Excel导出服务类"""
    
    def __init__(self):
        self.output_dir = settings.OUTPUT_DIR
    
    def create_extraction_excel(
        self,
        extraction_results: Dict[str, Any],
        filename_prefix: str = "报关单提取结果"
    ) -> str:
        """
        创建字段提取结果的Excel文件
        
        Args:
            extraction_results: 格式化后的提取结果
            filename_prefix: 文件名前缀
            
        Returns:
            生成的Excel文件路径
        """
        try:
            # 生成文件名
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = sanitize_filename(f"{filename_prefix}_{timestamp}.xlsx")
            filepath = os.path.join(self.output_dir, filename)
            
            # 创建工作簿
            wb = Workbook()
            
            # 1. 创建"提取结果"工作表
            ws_data = wb.active
            ws_data.title = "提取结果"
            self._create_data_sheet(ws_data, extraction_results)
            
            # 2. 创建"完整内容"工作表
            ws_content = wb.create_sheet(title="完整内容")
            self._create_content_sheet(ws_content, extraction_results)
            
            # 3. 创建"统计信息"工作表
            ws_summary = wb.create_sheet(title="统计信息")
            self._create_summary_sheet(ws_summary, extraction_results)
            
            # 4. 如果有错误，创建"错误信息"工作表
            if extraction_results.get("errors"):
                ws_errors = wb.create_sheet(title="错误信息")
                self._create_errors_sheet(ws_errors, extraction_results)
            
            # 保存文件
            wb.save(filepath)
            logger.info(f"Excel文件已生成: {filepath}")
            
            return filepath
            
        except Exception as e:
            logger.error(f"生成Excel文件失败: {str(e)}")
            raise Exception(f"Excel导出失败: {str(e)}")
    
    def _create_data_sheet(self, ws, results: Dict[str, Any]):
        """创建提取结果数据表"""
        # 设置标题行
        headers = ["字段名称", "提取值", "数据类型"]
        ws.append(headers)
        
        # 设置标题样式
        header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        header_font = Font(bold=True, color="FFFFFF", size=12)
        
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        
        # 填充数据
        fields_data = results.get("fields_data", {})
        row_num = 2
        
        for field_name, field_value in fields_data.items():
            # 如果是字典类型（通常是 {"text": "值"}）
            if isinstance(field_value, dict):
                display_value = field_value.get("text", str(field_value))
                data_type = "字符串"
            elif isinstance(field_value, list):
                display_value = str(field_value)
                data_type = "数组"
            else:
                display_value = str(field_value)
                data_type = type(field_value).__name__
            
            ws.cell(row=row_num, column=1, value=field_name)
            ws.cell(row=row_num, column=2, value=display_value)
            ws.cell(row=row_num, column=3, value=data_type)
            row_num += 1
        
        # 设置列宽
        ws.column_dimensions['A'].width = 30
        ws.column_dimensions['B'].width = 60
        ws.column_dimensions['C'].width = 15
        
        # 设置边框
        self._set_borders(ws, row_num - 1)
    
    def _create_content_sheet(self, ws, results: Dict[str, Any]):
        """创建完整内容工作表"""
        # 设置标题行
        headers = ["字段名称", "完整AI输出内容"]
        ws.append(headers)
        
        # 设置标题样式
        header_fill = PatternFill(start_color="70AD47", end_color="70AD47", fill_type="solid")
        header_font = Font(bold=True, color="FFFFFF", size=12)
        
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        
        # 填充数据
        raw_contents = results.get("raw_contents", {})
        row_num = 2
        
        for field_name, content in raw_contents.items():
            ws.cell(row=row_num, column=1, value=field_name)
            cell = ws.cell(row=row_num, column=2, value=content)
            cell.alignment = Alignment(wrap_text=True, vertical="top")
            row_num += 1
        
        # 设置列宽
        ws.column_dimensions['A'].width = 30
        ws.column_dimensions['B'].width = 100
        
        # 设置边框
        self._set_borders(ws, row_num - 1)
    
    def _create_summary_sheet(self, ws, results: Dict[str, Any]):
        """创建统计信息工作表"""
        # 设置标题
        ws.merge_cells('A1:B1')
        ws['A1'] = "提取统计信息"
        ws['A1'].font = Font(bold=True, size=14)
        ws['A1'].alignment = Alignment(horizontal="center", vertical="center")
        ws['A1'].fill = PatternFill(start_color="FFC000", end_color="FFC000", fill_type="solid")
        
        # 统计数据
        stats = [
            ("总字段数", results.get("total_fields", 0)),
            ("成功提取", results.get("successful_fields", 0)),
            ("提取失败", results.get("failed_fields", 0)),
            ("成功率", f"{results.get('successful_fields', 0) / max(results.get('total_fields', 1), 1) * 100:.2f}%"),
            ("生成时间", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        ]
        
        row_num = 3
        for label, value in stats:
            ws.cell(row=row_num, column=1, value=label).font = Font(bold=True)
            ws.cell(row=row_num, column=2, value=value)
            row_num += 1
        
        # 设置列宽
        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 30
    
    def _create_errors_sheet(self, ws, results: Dict[str, Any]):
        """创建错误信息工作表"""
        # 设置标题行
        headers = ["字段名称", "错误信息"]
        ws.append(headers)
        
        # 设置标题样式
        header_fill = PatternFill(start_color="C00000", end_color="C00000", fill_type="solid")
        header_font = Font(bold=True, color="FFFFFF", size=12)
        
        for col_num, header in enumerate(headers, 1):
            cell = ws.cell(row=1, column=col_num)
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(horizontal="center", vertical="center")
        
        # 填充错误数据
        errors = results.get("errors", {})
        row_num = 2
        
        for field_name, error_msg in errors.items():
            ws.cell(row=row_num, column=1, value=field_name)
            cell = ws.cell(row=row_num, column=2, value=error_msg)
            cell.alignment = Alignment(wrap_text=True, vertical="top")
            row_num += 1
        
        # 设置列宽
        ws.column_dimensions['A'].width = 30
        ws.column_dimensions['B'].width = 80
        
        # 设置边框
        self._set_borders(ws, row_num - 1)
    
    def _set_borders(self, ws, max_row: int):
        """为工作表设置边框"""
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        for row in ws.iter_rows(min_row=1, max_row=max_row):
            for cell in row:
                cell.border = thin_border

